﻿using SqlSugar;
using WMS_DATABASE_MYSQL.MappingClass;
using WMS_DATABASE_MYSQL.MappingClass.CategoryClass;
using WMS_DATABASE_MYSQL.MappingClass.PropertyClass;
using WMS_DATABASE_MYSQL.MappingClass.SystemClass;
using WMS_DATABASE_MYSQL.ModalClass;

namespace WMS_DATABASE_MYSQL
{
    /// <summary>
    /// 组合查询资产的所有状态，包括是否在库，最近一次的出库，最近一次的入库，是否报废等
    /// </summary>
    public class sqlsugar_assetsstatus
    {
        public readonly ISqlSugarClient db;
        public sqlsugar_assetsstatus(ISqlSugarClient db)
        {
            this.db = db;
        }
        /// <summary>
        /// 查询出所有物资的在库情况，需要与多个表进行联动
        /// </summary>
        /// <returns></returns>
        public async Task<List<AssetStatusDto>> GetAssetStatusesAsync()
        {
            var data=await db.Queryable<wms_assets_table>()
                .LeftJoin<wms_scrap_table>((a, d) => a.sn == d.sn)
                .Select((a, d) => new AssetStatusDto
                {
                    sn = a.sn,
                    assetsclass=a.assetsclass,
                    assetsnumber=a.assetsnumber,
                    assetsname=a.assetsname,
                    type=a.type,
                    calinterval=a.calinterval,
                    purchasetime=a.time,
                    lastoutboundtime=SqlFunc.Subqueryable<wms_outbound_table>().Where(b => b.sn == a.sn)
                    .OrderByDesc(b => b.time).Select(b => b.time),
                    lastoutboundtype= SqlFunc.Subqueryable<wms_outbound_table>().Where(b => b.sn == a.sn)
                    .OrderByDesc(b => b.time).Select(b => b.outboundclass),
                    borrower= SqlFunc.Subqueryable<wms_outbound_table>().Where(b => b.sn == a.sn)
                    .OrderByDesc(b => b.time).Select(b => b.borrower),
                    project = SqlFunc.Subqueryable<wms_outbound_table>().Where(b => b.sn == a.sn)
                    .OrderByDesc(b => b.time).Select(b => b.project),
                    vehicle = SqlFunc.Subqueryable<wms_outbound_table>().Where(b => b.sn == a.sn)
                    .OrderByDesc(b => b.time).Select(b => b.vehicle),
                    lastinboundtime= SqlFunc.Subqueryable<wms_inbound_table>().Where(b => b.sn == a.sn)
                    .OrderByDesc(b => b.time).Select(b => b.time),
                    scraptime =d.time,
                }).ToListAsync();
            data.ForEach(a => a.status = a.scraptime.HasValue ? "报废" : a.lastinboundtime >= a.lastoutboundtime ? "在库" : "出库");
            return data;
        }
    }
}
